package org.example.day20240905.dao;

import org.example.day20240905.model.Article;
import org.example.day20240905.model.Tag;
import org.example.day20240905.util.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class ArticleDao {
    public void createArticle(Article article) {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sqlInsert = "insert into articles values(?,?,?,?,?,?)";
            ps = connection.prepareStatement(sqlInsert);
            ps.setString(1, article.getId());
            ps.setString(2, article.getUser_id());
            ps.setString(3, article.getTitle());
            ps.setString(4, article.getBody());
            ps.setString(5, article.getCreated_at());
            ps.setString(6, article.getUpdated_at());
            ps.executeUpdate();
            List<String> tagLists = new ArrayList<>();
            for (Tag tag : article.getTags()) {
                String sqlTag = "select id from tags where name = ?";
                ps = connection.prepareStatement(sqlTag);
                ps.setString(1, tag.getName());
                ps.executeQuery();
                rs = ps.getResultSet();
                while (rs.next()) {
                    String tag_id = rs.getString("id");
                    tagLists.add(tag_id);
                    if (tag_id.equals(null)) {
                        String sqlInsertTag = "insert into tags values(?,?)";
                        ps = connection.prepareStatement(sqlInsertTag);
                        ps.setString(1, tag.getId());
                        ps.setString(2, tag.getName());
                        ps.executeUpdate();
                    }
                }
            }
            for (String tagList : tagLists) {
                String sqlInsertTag = "insert into article_tags values(?,?)";
                ps = connection.prepareStatement(sqlInsertTag);
                ps.setString(1, article.getId());
                ps.setString(2, tagList);
                ps.executeUpdate();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                ps.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    public Map<String,List<Article>> checkArticle() {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        Map<String,List<Article>> ArticleLists = new LinkedHashMap<String,List<Article>>();
        try {
            String sqlTag = "SELECT" +
                    " articles.id," +
                    " articles.user_id," +
                    " articles.title," +
                    " articles.body," +
                    " articles.created_at," +
                    " articles.updated_at," +
                    " IFNULL( GROUP_CONCAT( tags.NAME ), '' ) AS tag_name," +
                    " users.username" +
                    " FROM" +
                    " articles" +
                    " LEFT JOIN article_tags ON article_tags.article_id = articles.id" +
                    " LEFT JOIN tags ON tags.id = article_tags.tag_id " +
                    " LEFT JOIN users ON articles.user_id = users.id" +
                    " GROUP BY articles.id";
            ps = connection.prepareStatement(sqlTag);
            ps.executeQuery();
            rs = ps.getResultSet();
            List<Tag> tagList = new ArrayList<>();
            List<Article> articleList = new ArrayList<>();
            int number = 1;
            while (rs.next()) {
                Article article = new Article();
                Tag tag = new Tag();
                article.setNumber(number++);
                article.setId(rs.getString("id"));
                article.setUser_id(rs.getString("user_id"));
                article.setTitle(rs.getString("title"));
                article.setBody(rs.getString("body"));
                article.setCreated_at(rs.getString("created_at"));
                article.setUpdated_at(rs.getString("updated_at"));
                String tags_name = rs.getString("tag_name");
                String username = rs.getString("username");
                String[] tag_name = tags_name.split(",");
                for (String tags : tag_name){
                    tag.setName(tags);
                    tag.setId("");
                }
                tagList.add(tag);
                article.setTags(tagList);
                articleList.add(article);
                ArticleLists.put(username,articleList);
            }
            return ArticleLists;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                ps.close();
                rs.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}
